IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserToAccessClinicalTrials]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserToAccessClinicalTrials]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetUserToAccessClinicalTrials (
		@domainUserId			INTEGER,
		@budgetId				INTEGER
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @pendingBudgetReadAccessBit INT
DECLARE @finalBudgetReadAccessBit INT
DECLARE @pendingBudgetModifyAccessBit INT
DECLARE @budgetResourceType int

set @budgetResourceType = 3
SET @pendingBudgetReadAccessBit = 8192
SET @finalBudgetReadAccessBit = 4096
SET @pendingBudgetModifyAccessBit = 32768

	--first if exists get it in portfolio
	if exists(select 1 from ClinicalTrialBudget b							
							INNER JOIN AdmPortfolio ap
							ON ap.AdmPersonId = b.PIPersonId
							INNER JOIN AdmRights_Map apm
							ON ap.AdmRightsMapId = apm.Id
							INNER JOIN FwkResource fr
							on fr.Id = b.FwkResourceId
							AND fr.FwkResourceTypeId = 3
							WHERE apm.fwkDomainUserId = 8299
							and b.Id = 152)
	BEGIN
		SELECT DISTINCT 
			 	fr.Id  as ResourceId
				,Cast(b.Id as VARCHAR) as RecordNumber
				,app.LastName + ', ' +  app.FirstName as PIName
				,fdu.UserName
				,CASE 
					WHEN ((apm.RightBitmask & @pendingBudgetReadAccessBit) = @pendingBudgetReadAccessBit) THEN 1 
					ELSE 0
				END as IsReadPending
				,CASE 
					WHEN ((apm.RightBitmask & @finalBudgetReadAccessBit) = @finalBudgetReadAccessBit) THEN 1 
					ELSE 0
				END as IsReadFinal
				,CASE 
					WHEN ((apm.RightBitmask & @pendingBudgetModifyAccessBit) = @pendingBudgetModifyAccessBit) THEN 1 
					ELSE 0
				END AS IsModifyPending
				,apm.FwkDomainUserId as UserDomainId			
		FROM ClinicalTrialBudget b			
			INNER JOIN RefCode r
			ON r.RefCode = b.StatusCode			
			INNER JOIN AdmPortfolio ap
			ON ap.AdmPersonId = b.PIPersonId
			INNER JOIN AdmRights_Map apm
			ON ap.AdmRightsMapId = apm.Id
			INNER JOIN FwkResource fr
			on fr.Id = b.FwkResourceId
			AND fr.FwkResourceTypeId = 3
			INNER JOIN 	FwkDomainUser fdu
			ON fdu.Id = apm.FwkDomainUserId	
			INNER JOIN AdmPerson app
			on app.FwkDomainUserId = apm.FwkDomainUserId
			WHERE apm.fwkDomainUserId != @domainUserId
			and b.Id = @budgetId

	END
	ELSE
	BEGIN
	-- get list of budgets rights from record level
		SELECT DISTINCT 
			 	p.FwkResourceId  as ResourceId
				,Cast(p.Id as VARCHAR) as RecordNumber
				,ap.LastName + ', ' +  ap.FirstName as PIName
				,fdu.UserName
				,CASE 
					WHEN ((fdum.AccessRightBitmask & @pendingBudgetReadAccessBit) = @pendingBudgetReadAccessBit) THEN 1 
					ELSE 0
				END as IsReadPending
				,CASE 
					WHEN ((fdum.AccessRightBitmask & @finalBudgetReadAccessBit) = @finalBudgetReadAccessBit) THEN 1 
					ELSE 0
				END as IsReadFinal
				,CASE 
					WHEN ((fdum.AccessRightBitmask & @pendingBudgetModifyAccessBit) = @pendingBudgetModifyAccessBit) THEN 1 
					ELSE 0
				END AS IsModifyPending
				,fdum.FwkDomainUserId as UserDomainId		
		FROM ClinicalTrialBudget p
		INNER JOIN FwkResource_DomainUser_Map fdum -- urc
		ON fdum.FwkResourceId = p.FwkResourceId
		INNER JOIN FwkResource fr
		on fr.Id = fdum.FwkResourceId
		AND fr.FwkResourceTypeId = @budgetResourceType
		INNER JOIN admPerson ap 
		ON fdum.FwkDomainUserId = ap.FwkDomainUserId
		INNER JOIN 	FwkDomainUser fdu
		ON fdu.Id = fdum.FwkDomainUserId		
		WHERE fdum.FwkDomainUserId != @domainUserId
			and p.Id = @budgetId
	end

END
GO